Mastering SQL for Oracle Fusion HCM – Chapter 14. SQL Advanced Data Manipulation Techniques in Fusion HCM

 

✅Chapter 14. SQL Advanced Data Manipulation Techniques in Fusion HCM

Advanced SQL Data Manipulation in Oracle Fusion HCM – CASE, Correlated Subqueries & More

Master advanced SQL data manipulation techniques in Oracle Fusion HCM including CASE statements, correlated subqueries, conditional logic, and performance tips.

---

🔹 Introduction

As your SQL skills grow, simple SELECT, JOIN, or WHERE clauses aren’t enough to solve real-world business problems in Oracle Fusion HCM. This chapter introduces advanced data manipulation techniques, including:

• Conditional logic with CASE expressions

• Correlated subqueries

• Advanced WHERE filtering and nesting

• Multi-column expressions

• Performance tuning for complex queries

These tools empower you to build highly dynamic and intelligent SQL reports that address Fusion HCM’s multi-dimensional data model.

---

🔹 1. CASE Expressions (Conditional Logic)

📘 Syntax:

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE
default_result END 

✅ Example: Display Custom Employee Categories Based on Salary

SELECT person_number,
       full_name,
       salary,
       CASE
         WHEN salary > 100000 THEN 'High Earner'
         WHEN salary BETWEEN 50000 AND 100000 THEN 'Mid Earner'
         ELSE 'Low Earner'
       END AS salary_category
FROM   per_all_assignments_m 

💡 Real-Time Use:

Generate reports for workforce segmentation based on earnings.

---

🔹 2. Correlated Subqueries

A correlated subquery is a subquery that uses values from the outer query.

✅ Example: Fetch Employees Who Earn More Than the Average Salary in Their Department

SELECT person_number,
       full_name,
       department_id,
       salary
FROM   per_all_assignments_m e
WHERE  salary > (SELECT AVG(salary)
                 FROM   per_all_assignments_m
                 WHERE  department_id = e.department_id) 

💡 Real-Time Use:

Identify top performers or outliers in a department.

---

🔹 3. Nested and Inline Subqueries

✅ Example: Inline Subquery to Get Latest Assignment Status for Employees

SELECT person_number,
       full_name,
       (SELECT assignment_status_type
        FROM   per_all_assignments_m a2
        WHERE  a2.person_id = e.person_id
        ORDER  BY effective_start_date desc
        FETCH first 1 ROWS only) AS latest_status
FROM   per_all_people_f e 

💡 Real-Time Use:

Report on employees’ most recent assignment data without complex joins.

---

🔹 4. Advanced Filtering with EXISTS / NOT EXISTS

✅ Example: List Employees Without Any Assignments

SELECT person_number,
       full_name
FROM   per_all_people_f p
WHERE  NOT EXISTS (SELECT 1
                   FROM   per_all_assignments_m a
                   WHERE  a.person_id = p.person_id) 

💡 Real-Time Use:

Track inactive or unassigned employees.

---

🔹 5. Using Expressions in SELECT for Derived Data

✅ Example: Display Tenure in Years

SELECT person_number,
       full_name,
       hire_date,
       ROUND(MONTHS_BETWEEN(SYSDATE, hire_date) / 12, 2) AS years_of_service
FROM   per_all_assignments_m 

💡 Real-Time Use:

Calculate years of service, useful for retirement eligibility reports.

---

🔹 6. Conditional Aggregations

✅ Example: Count Active and Inactive Employees in Each Department

SELECT department_id,
       COUNT(CASE
               WHEN assignment_status_type = 'ACTIVE' THEN 1
             END) AS active_count,
       COUNT(CASE
               WHEN assignment_status_type != 'ACTIVE' THEN 1
             END) AS inactive_count
FROM   per_all_assignments_m
GROUP  BY department_id 

💡 Real-Time Use:

Department health check — active vs inactive ratios.

---

🔹 7. Multi-Table Insert (if allowed in HCM extracts)

In Fusion HCM reporting tools like BIP, this isn’t commonly used directly, but advanced extract configurations may simulate this logic using split SQL blocks or rowsets.

---

🔹 8. Performance Tuning Tips for Complex Queries in Fusion HCM

Here’s how to make sure your complex queries perform well on large Oracle Fusion HCM datasets:

✅ a. Use Bounded Date Ranges

Limit your data using TRUNC(SYSDATE) or specific ranges.

WHERE effective_start_date <= trunc(SYSDATE)
AND
effective_end_date >= trunc(SYSDATE)

✅ b. Use EXISTS Instead of IN for Large Subqueries

EXISTS performs better with correlated logic than IN.

✅ c. Avoid SELECT *

Always use explicit column names for better parsing and performance.

✅ d. Use Indexable Fields in Joins and Filters

Leverage fields like person_id, assignment_id, and effective_start_date.

✅ e. Use WITH Clause for Common Table Expressions (CTEs)

For modularizing complex queries:

WITH active_emps
     AS (SELECT person_id
         FROM   per_all_assignments_m
         WHERE  assignment_status_type = 'ACTIVE')
SELECT p.full_name
FROM   per_all_people_f p,
       active_emps a
WHERE  p.person_id = a.person_id 

---

🔹 Summary

✅ Use CASE for conditional logic and classification.
✅ Apply correlated subqueries for row-level filtering across related data.
✅ Use EXISTS, LAG, LEAD, and derived columns to handle complex business logic.
✅ Optimize your query using bounded dates, indexed columns, and CTEs in Fusion HCM.

---

🔹 Next Steps

• Previous Chapter:   Mastering SQL for Oracle Fusion HCM – Chapter 13. SQL Window Functions and Analytics in Fusion HCM

• Next Chapter: Mastering SQL for Oracle Fusion HCM – Chapter 15. SQL in BI Publisher and OTBI: Querying Oracle Fusion HCM Data Sources

Tags: #AdvancedSQL, #FusionHCM, #CASE, #CorrelatedSubquery, #SQLTuning, #CTE, #ConditionalLogic, #PerformanceOptimization